package cn.yunhe.dao.temp;

import cn.yunhe.dao.HouseDao;
import cn.yunhe.pojo.House;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class HouseDaoImp2NamedParameterJdbcTemplate1 implements HouseDao {
    private NamedParameterJdbcTemplate jdbcTemplate;

    public NamedParameterJdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(NamedParameterJdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public House searchHouse(int hid) {
        String sql="select h.*,m.*,t.*,s.*,d.*";
        sql+="from house h ";
        sql+="inner join member m on h.hmid=m.mid ";
        sql+="inner join type t on h.htid=t.tid ";
        sql+="inner join street s on h.hsid=s.sid ";
        sql+="inner join district d on s.sdid=d.did ";
        sql+="where hid=:hid1 ";
        sql+="order by hid";

        Map map=new HashMap();
        map.put("hid1",hid);
        List<House>houseList=jdbcTemplate.query(sql,map,houseRowMapper);
        return houseList.size()>0?houseList.get(0):null;
    }

    @Override
    public List<House> houseList() {
        String sql="select h.*,m.*,t.*,s.*,d.*";
        sql+="from house h ";
        sql+="inner join member m on h.hmid=m.mid ";
        sql+="inner join type t on h.htid=t.tid ";
        sql+="inner join street s on h.hsid=s.sid ";
        sql+="inner join district d on s.sdid=d.did ";
        sql+="order by hid";

        System.out.println(sql);
        List<House>houseList= jdbcTemplate.query(sql,new HashMap(),houseRowMapper);
        return houseList;
    }

    @Override
    public List<House> houseList(House house) {
        StringBuilder sql=new StringBuilder();
        sql.append("select h.*,m.*,t.*,s.*,d.*");
        sql.append("from house h ");
        sql.append("inner join member m on h.hmid=m.mid ");
        sql.append("inner join type t on h.htid=t.tid ");
        sql.append("inner join street s on h.hsid=s.sid ");
        sql.append("inner join district d on s.sdid=d.did ");
        sql.append("where 1=1  ");

        Map map=new HashMap();
        if (house!=null){
            if (house.getHtitle()!=null&&house.getHtitle().trim().length()>0){
                sql.append("and h.htitle like :htitle1 ");
                map.put("htitle1","%"+house.getHtitle()+"%");
            }
            if (house.getHprice()>0){
                sql.append("and h.hprice > :hprice1 ");
                map.put("hprice1",house.getHprice());
            }
        }
        sql.append("order by hid ");
        System.out.println(sql);

        List<House>houseList= jdbcTemplate.query(sql.toString(),map,houseRowMapper);
        return houseList;
    }

    @Override
    public boolean insertHouse(House house) {
        String sql="insert into house(htitle,hprice,hfloorage,hpubdate,hcontact,hdesc,hflag,hmid,htid,hsid)" ;
        sql+="values(:htitle,:hprice,:hfloorage,:hpubdate,:hcontact,:hdesc,:hflag,:hmid,:htid,:hsid)";
        System.out.println(sql);

        Map map=new HashMap();
        {
            map.put("htitle",house.getHtitle());
            map.put("htitle",house.getHprice());
            map.put("hfloorage",house.getHfloorage());
            map.put("hpubdate",house.getHpubdate());
            map.put("hcontact",house.getHcontact());
            map.put("hdesc",house.getHdesc());
            map.put("hflag",house.getHflag());
            map.put("hmid",house.getMember().getMid());
            map.put("htid",house.getType().getTid());
            map.put("hsid",house.getStreet().getSid());
        };

        int flag1=jdbcTemplate.update(sql,map);
        boolean flag2=false;
        if (flag1>0){
            flag2=true;
        }
        return flag2;
    }

    @Override
    public boolean updateHouse(House house) {
        return false;
    }

    @Override
    public boolean deleteHouse(House house) {
        return false;
    }
}
